begin transaction

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTask2User]') AND type in (N'U'))
DROP TABLE tTask2User
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tModule2User]') AND type in (N'U'))
DROP TABLE tModule2User
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tProject2User]') AND type in (N'U'))
DROP TABLE tProject2User
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTask2Module]') AND type in (N'U'))
DROP TABLE tTask2Module
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tModule2Project]') AND type in (N'U'))
DROP TABLE tModule2Project
GO
--------------------tables--------------------------------

--settings
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tSettings]') AND type in (N'U'))
DROP TABLE [dbo].[tSettings]
GO
CREATE TABLE [dbo].[tSettings](
	[Name] [varchar](255) PRIMARY KEY NOT NULL,
	[Value] [varchar](255) NULL,
) ON [PRIMARY]

--tUser
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tUser]') AND type in (N'U'))
DROP TABLE [dbo].[tUser]
GO
CREATE TABLE [dbo].[tUser](
	[ID] [int] PRIMARY KEY NOT NULL,
	[ident] [varchar](255), -- user name for login
	[password] [varchar](255),
	[firstName] [varchar](255) NOT NULL,
	[lastName] [varchar](255) NOT NULL,
	[isActive] [bit] NOT NULL DEFAULT 'false', 
	[updated] [datetime],
	[created][datetime] NOT NULL DEFAULT getutcdate()
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_tUser_updated]'))
DROP TRIGGER [dbo].[tr_tUser_updated]
GO
CREATE TRIGGER [tr_tUser_updated] ON [tUser]
FOR INSERT,UPDATE AS
UPDATE [tUser] SET [tUser].updated = (getutcdate()) FROM [tUser]
INNER JOIN Inserted ON Inserted.ID = [tUser].ID
GO

--tPermission
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tPermission]') AND type in (N'U'))
DROP TABLE [dbo].[tPermission]
GO
CREATE TABLE [dbo].[tPermission](
	[ID] [int] PRIMARY KEY NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[updated] [datetime],
	[created][datetime] NOT NULL DEFAULT getutcdate()
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_tPermission_updated]'))
DROP TRIGGER [dbo].[tr_tPermission_updated]
GO
CREATE TRIGGER [tr_tPermission_updated] ON [tUser]
FOR INSERT,UPDATE AS
UPDATE [tr_tPermission_updated] SET [tr_tPermission_updated].updated = (getutcdate()) FROM [tr_tPermission_updated]
INNER JOIN Inserted ON Inserted.ID = [tr_tPermission_updated].ID
GO


--Task
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTask]') AND type in (N'U'))
DROP TABLE [dbo].[tTask]
GO
CREATE TABLE [dbo].[tTask](
	[ID] [int] PRIMARY KEY NOT NULL,
	[name] [varchar](255) NOT NULL,
	[description] [varchar](255) NOT NULL,
	[startDate] [datetime] ,
	[endDate] [datetime] ,
	[updated] [datetime] ,
	[created][datetime] NOT NULL DEFAULT getutcdate()
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_tTask_updated]'))
DROP TRIGGER [dbo].[tr_tTask_updated]
GO
CREATE TRIGGER [tr_tTask_updated] ON [tTask]
FOR INSERT,UPDATE AS
UPDATE [tTask] SET [tTask].updated = (getutcdate()) FROM [tTask]
INNER JOIN Inserted ON Inserted.ID = [tTask].ID
GO

--Module
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tModule]') AND type in (N'U'))
DROP TABLE [dbo].[tModule]
GO
CREATE TABLE [dbo].[tModule](
	[ID] [int] PRIMARY KEY NOT NULL,
	[name] [varchar](255) NOT NULL,
	[description] [varchar](255) NOT NULL,
	[startDate] [datetime] ,
	[endDate] [datetime] ,
	[updated] [datetime] ,
	[created][datetime] NOT NULL DEFAULT getutcdate()
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_tModule_updated]'))
DROP TRIGGER [dbo].[tr_tModule_updated]
GO
CREATE TRIGGER [tr_tModule_updated] ON [tModule]
FOR INSERT,UPDATE AS
UPDATE [tModule] SET [tModule].updated = (getutcdate()) FROM [tModule]
INNER JOIN Inserted ON Inserted.ID = [tModule].ID
GO

--Project
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tProject]') AND type in (N'U'))
DROP TABLE [dbo].[tProject]
GO
CREATE TABLE [dbo].[tProject](
	[ID] [int] PRIMARY KEY NOT NULL,
	[name] [varchar](255) NOT NULL,
	[description] [varchar](255) NOT NULL,
	[startDate] [datetime] ,
	[endDate] [datetime] ,
	[updated] [datetime] ,
	[created][datetime] NOT NULL DEFAULT getutcdate()
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_tProject_updated]'))
DROP TRIGGER [dbo].[tr_tProject_updated]
GO
CREATE TRIGGER [tr_tProject_updated] ON [tProject]
FOR INSERT,UPDATE AS
UPDATE [tProject] SET [tProject].updated = (getutcdate()) FROM [tProject]
INNER JOIN Inserted ON Inserted.ID = [tProject].ID
GO

------------------link tables-----------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tPermission2User]') AND type in (N'U'))
DROP TABLE [dbo].[tPermission2User]
GO
CREATE TABLE [dbo].[tPermission2User](
	[permissionID] [int] NOT NULL FOREIGN KEY REFERENCES tPermission(ID),
	[userID] [int] NOT NULL FOREIGN KEY REFERENCES tUser(ID)
	primary key ([permissionID], [userID])
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTask2User]') AND type in (N'U'))
DROP TABLE [dbo].[tTask2User]
GO
CREATE TABLE [dbo].[tTask2User](
	[taskID] [int] NOT NULL FOREIGN KEY REFERENCES tTask(ID),
	[userID] [int] NOT NULL FOREIGN KEY REFERENCES tUser(ID)
	primary key ([taskID], [userID])
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tModule2User]') AND type in (N'U'))
DROP TABLE [dbo].[tModule2User]
GO
CREATE TABLE [dbo].[tModule2User](
	[ModuleID] [int] NOT NULL FOREIGN KEY REFERENCES tModule(ID),
	[userID] [int] NOT NULL FOREIGN KEY REFERENCES tUser(ID)
	primary key ([ModuleID], [userID])
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tProject2User]') AND type in (N'U'))
DROP TABLE [dbo].[tProject2User]
GO
CREATE TABLE [dbo].[tProject2User](
	[ProjectID] [int] NOT NULL FOREIGN KEY REFERENCES tProject(ID),
	[userID] [int] NOT NULL FOREIGN KEY REFERENCES tUser(ID)
	primary key ([ProjectID], [userID])
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTask2Module]') AND type in (N'U'))
DROP TABLE [dbo].[tTask2Module]
GO
CREATE TABLE [dbo].[tTask2Module](
	[taskID] [int] NOT NULL FOREIGN KEY REFERENCES tTask(ID),
	[moduleID] [int] NOT NULL FOREIGN KEY REFERENCES tModule(ID)
	primary key ([taskID], [moduleID])
)
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tModule2Project]') AND type in (N'U'))
DROP TABLE [dbo].[tModule2Project]
GO
CREATE TABLE [dbo].[tModule2Project](
	[moduleID] [int] NOT NULL FOREIGN KEY REFERENCES tModule(ID),
	[ProjectID] [int] NOT NULL FOREIGN KEY REFERENCES tProject(ID)
	primary key ([moduleID], [ProjectID])
)
GO

-----------------------Stored Procedures ------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetUser]
GO
CREATE PROCEDURE [dbo].[spGetUser] (@ident varchar(255))
AS
BEGIN
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	select * from tUser where ident = @ident
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spLoginUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spLoginUser]
GO
CREATE PROCEDURE [dbo].[spLoginUser] (@ident varchar(255), @password varchar(255))
AS
BEGIN
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    DECLARE @id int
	set @id = (select id from tUser where [ident] = @ident and [password] = @password)
	--print @id
	IF (@@rowcount = 1)BEGIN
		select 1 as result
    END
    ELSE BEGIN
		select 0 as result
    END
END
GO

--rollback
commit

--exec spGetUser @ident = ''
--exec spLoginUser @ident = 'dsa', @password = ''
select id from tUser where [ident] = 'ds' and [password] = ''